package com.lpit.mapper;

import com.lpit.dto.BarChartDto;
import com.lpit.pojo.Article;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
* @author Administrator
* @description 针对表【article(文章表)】的数据库操作Mapper
* @createDate 2023-06-08 11:06:00
* @Entity com.lpit.pojo.ArticleDto
*/
@Mapper
public interface ArticleMapper extends BaseMapper<Article> {
    /**
     * @apiNote 查询文章的总数量
     * @return Integer
     */
    @Select("select count(id) from article")
    Integer selectArticlesNumber();
    /**
     * @apiNote 查询评论的总数量
     * @return Integer
     */
    @Select("select count(id) from comment")
    Integer selectCommentsNumber();
    /**
     * @apiNote 查询评浏览量
     * @return Integer
     */
    @Select("SELECT count(id) FROM browsing_history WHERE article_status='浏览'")
    Integer selectReadingQuantity();
    /**
     * @apiNote 查询转发量
     * @return Integer
     */
    @Select("SELECT count(id) FROM browsing_history WHERE article_status='转发'")
    Integer selectForwardingNumber();
    /**
     * @author yaochen
     * @apiNote 柱状图，按时间查询文章数量。
     * @return List<String>
     */
    @Select("SELECT DATE_FORMAT(article_time,'%d') as date , COUNT(*) as number " +
            "FROM article " +
            "WHERE DATE_FORMAT(article_time,'%Y-%m-%d') >= DATE_FORMAT(#{startDate},'%Y-%m-%d') AND DATE_FORMAT(article_time,'%Y-%m-%d') <= DATE_FORMAT(#{endDate},'%Y-%m-%d') " +
            "GROUP BY DATE_FORMAT(article_time,'%d')")
    List<BarChartDto> selectArticlesNumberTime(String startDate,String endDate);


    /**
     * @author yaochen
     * @apiNote 柱状图，按时间查询阅读量。
     * @return List<String>
     */
    @Select("SELECT DATE_FORMAT(datetime,'%d') as date , COUNT(*) as number " +
            "FROM browsing_history "+
            "WHERE DATE_FORMAT(datetime,'%Y-%m-%d') >= DATE_FORMAT(#{startDate},'%Y-%m-%d')" +
            "  AND DATE_FORMAT(datetime,'%Y-%m-%d') <= DATE_FORMAT(#{endDate},'%Y-%m-%d')" +
            "GROUP BY DATE_FORMAT(datetime,'%d');")
    List<BarChartDto> selectReadingQuantityTime(String startDate,String endDate);
    /**
     * @author yaochen
     * @apiNote 柱状图，按时间查询阅读量。
     * @return List<String>
     */
    @Select("SELECT DATE_FORMAT(comment_time,'%d') as date , COUNT(*) as number " +
            "FROM comment "+
            "WHERE DATE_FORMAT(comment_time,'%Y-%m-%d') >= DATE_FORMAT(#{startDate},'%Y-%m-%d')" +
            "  AND DATE_FORMAT(comment_time,'%Y-%m-%d') <= DATE_FORMAT(#{endDate},'%Y-%m-%d')" +
            "GROUP BY DATE_FORMAT(comment_time,'%d');")
    List<BarChartDto> selectCommentsNumberTime(String startDate,String endDate);

    /**
     * 文章置顶
     */
    @Select("UPDATE article SET is_top =1 WHERE id = #{id};")
    Integer findByIdIsTop (Integer id);
}